import datetime
import time
from backend.app.app.db.session_public import session_pub
import backend.app.app.models.db_owi_public_models as md_pub
from backend.app.app.api.effect_pridict.NN_bp import BPNeuralNetwork
from sklearn.preprocessing import MinMaxScaler
import math

def dvpunit_name_tree(dvp_list, DvpUnit_Name):
    dvp_list = sorted(dvp_list, key=lambda x: x.get("DvpUnit_Rank"))
    num = 1
    temp_list = list()
    for dvp in dvp_list:
        temp_dict = dict()
        if dvp['Belong_DvpUnit_Name'] == DvpUnit_Name and dvp['DvpUnit_Name'] != DvpUnit_Name:
            temp_dict.update({
                'id': f'{dvp["DvpUnit_ID"]}{num}',
                'title': dvp['DvpUnit_Name'],
            })
            num += 1
            child = dvpunit_name_tree(dvp_list, dvp['DvpUnit_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            temp_list.append(temp_dict)
    return temp_list

#   获取样本数据 hzc改
def get_sample_data_all(StimType_Name, Model_Name):
    try:
        data = session_pub.query(
            md_pub.TbWellprojStimeforcastBpnn.X1,
            md_pub.TbWellprojStimeforcastBpnn.X2,
            md_pub.TbWellprojStimeforcastBpnn.X3,
            md_pub.TbWellprojStimeforcastBpnn.X4,
            md_pub.TbWellprojStimeforcastBpnn.X5,
            md_pub.TbWellprojStimeforcastBpnn.X6,
            md_pub.TbWellprojStimeforcastBpnn.X7,
            md_pub.TbWellprojStimeforcastBpnn.X8,
            md_pub.TbWellprojStimeforcastBpnn.X9,
            md_pub.TbWellprojStimeforcastBpnn.X10,
            md_pub.TbWellprojStimeforcastBpnn.X11,
            md_pub.TbWellprojStimeforcastBpnn.X12,
            md_pub.TbWellprojStimeforcastBpnn.X13,
            md_pub.TbWellprojStimeforcastBpnn.X14,
            md_pub.TbWellprojStimeforcastBpnn.X15,
            md_pub.TbWellprojStimeforcastBpnn.X16,
            md_pub.TbWellprojStimeforcastBpnn.X17,
            md_pub.TbWellprojStimeforcastBpnn.X18,
            md_pub.TbWellprojStimeforcastBpnn.X19,
            md_pub.TbWellprojStimeforcastBpnn.X20,
            md_pub.TbWellprojStimeforcastBpnn.X21,
            md_pub.TbWellprojStimeforcastBpnn.X22,
            md_pub.TbWellprojStimeforcastBpnn.X23,
            md_pub.TbWellprojStimeforcastBpnn.X24,
            md_pub.TbWellprojStimeforcastBpnn.X25,
            md_pub.TbWellprojStimeforcastBpnn.X26,
            md_pub.TbWellprojStimeforcastBpnn.X27,
            md_pub.TbWellprojStimeforcastBpnn.X28,
            md_pub.TbWellprojStimeforcastBpnn.X29,
            md_pub.TbWellprojStimeforcastBpnn.X30
        ).filter(
            md_pub.TbWellprojStimeforcastBpnn.StimType_Name == StimType_Name,
            md_pub.TbWellprojStimeforcastBpnn.Model_Name == Model_Name
        ).all()
        sample_sign_result = session_pub.query(
            md_pub.TbWellprojStimeforcastBpnn.Sample_Sign
        ).filter(
            md_pub.TbWellprojStimeforcastBpnn.StimType_Name == StimType_Name,
            md_pub.TbWellprojStimeforcastBpnn.Model_Name == Model_Name
        ).all()
        sample_sign = []
        for item in sample_sign_result:
            sample_sign.append(item[0])
        return data, sample_sign
    except Exception as e:
        print(e)
        session_pub.rollback()
        print("获取数据失败")
        return False


def calculate_p(params):
    """
    hzc
    计算P10， P90
    """
    # 只给p10, p90赋值一次
    p10, p90 = -1, -1
    if len(params) == 1:
        # 若只有一种数值，则P10=0, P90=该数值
        p10 = p90 = params.index[0]
        return p10, p90
    temp_p = 0  # 临时参数，用来计算累计概率
    for row in params.index:
        temp_p = temp_p + params[row]
        if p10 != -1 and p90 != -1:
            break
        if p10 == -1:
            # 当概率为10%的时候，将该值赋给p10
            if temp_p >= 0.1:
                p10 = row
        if p90 == -1:
            if temp_p >= 0.9:
                p90 = row
    return p10, p90


#  根据筛选条件获取样本数据
def get_sample_data(unit_name, name, start, end):
    try:
        # 根据开发单元查询隶属的所有井
        well_name_result = session_pub.query(
            md_pub.TbWellbasic.Well_Name
        ).filter(
            md_pub.TbWellbasic.Belong_DvpUnit_Name == unit_name
        ).all()
        well_name = []
        for item in well_name_result:
            well_name.append(item[0])
        data = session_pub.query(
            md_pub.TbWellprojStimeforcastBpnn.X1,
            md_pub.TbWellprojStimeforcastBpnn.X2,
            md_pub.TbWellprojStimeforcastBpnn.X3,
            md_pub.TbWellprojStimeforcastBpnn.X4,
            md_pub.TbWellprojStimeforcastBpnn.X5,
            md_pub.TbWellprojStimeforcastBpnn.X6,
            md_pub.TbWellprojStimeforcastBpnn.X7,
            md_pub.TbWellprojStimeforcastBpnn.X8,
            md_pub.TbWellprojStimeforcastBpnn.X9,
            md_pub.TbWellprojStimeforcastBpnn.X10,
            md_pub.TbWellprojStimeforcastBpnn.X11,
            md_pub.TbWellprojStimeforcastBpnn.X12,
            md_pub.TbWellprojStimeforcastBpnn.X13,
            md_pub.TbWellprojStimeforcastBpnn.X14,
            md_pub.TbWellprojStimeforcastBpnn.X15,
            md_pub.TbWellprojStimeforcastBpnn.X16,
            md_pub.TbWellprojStimeforcastBpnn.X17,
            md_pub.TbWellprojStimeforcastBpnn.X18,
            md_pub.TbWellprojStimeforcastBpnn.X19,
            md_pub.TbWellprojStimeforcastBpnn.X20,
            md_pub.TbWellprojStimeforcastBpnn.X21,
            md_pub.TbWellprojStimeforcastBpnn.X22,
            md_pub.TbWellprojStimeforcastBpnn.X23,
            md_pub.TbWellprojStimeforcastBpnn.X24,
            md_pub.TbWellprojStimeforcastBpnn.X25,
            md_pub.TbWellprojStimeforcastBpnn.X26,
            md_pub.TbWellprojStimeforcastBpnn.X27,
            md_pub.TbWellprojStimeforcastBpnn.X28,
            md_pub.TbWellprojStimeforcastBpnn.X29,
            md_pub.TbWellprojStimeforcastBpnn.X30
        ).filter(
            md_pub.TbWellprojStimeforcastBpnn.Well_Name.in_(well_name),
            md_pub.TbWellprojStimeforcastBpnn.StimType_Name == name,
            md_pub.TbWellprojStimeforcastBpnn.End_Date >= start,
            md_pub.TbWellprojStimeforcastBpnn.End_Date <= end
        ).order_by(
            md_pub.TbWellprojStimeforcastBpnn.End_Date
        ).all()
        sample_sign_result = session_pub.query(
            md_pub.TbWellprojStimeforcastBpnn.Sample_Sign
        ).filter(
            md_pub.TbWellprojStimeforcastBpnn.Well_Name.in_(well_name),
            md_pub.TbWellprojStimeforcastBpnn.StimType_Name == name,
            md_pub.TbWellprojStimeforcastBpnn.End_Date >= start,
            md_pub.TbWellprojStimeforcastBpnn.End_Date <= end
        ).order_by(
            md_pub.TbWellprojStimeforcastBpnn.End_Date
        ).all()
        sample_sign = []
        for item in sample_sign_result:
            sample_sign.append(item[0])
        return data, sample_sign
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)
        session_pub.rollback()
        print("获取数据失败")
        return False


#   获取所有开发单元信息
def get_dvp_info():
    try:
        data = session_pub.query(md_pub.TbDvpunitbaisc.DvpUnit_ID,
                                 md_pub.TbDvpunitbaisc.DvpUnit_Name,
                                 md_pub.TbDvpunitbaisc.Belong_DvpUnit_Name,
                                 md_pub.TbDvpunitbaisc.DvpUnit_Rank).all()
        data = [dict(zip(result.keys(), result)) for result in data]
        end = session_pub.query(md_pub.TbWellprojStimeforcastBpnn.End_Date).order_by(
            md_pub.TbWellprojStimeforcastBpnn.End_Date).all()
        return data, end
    except Exception:
        session_pub.rollback()
        print("获取数据失败")
        return False, False


# 读取所有模型名称 tb_wellproj_stimeforcast_bpnn
def stimforcast_readModelName():
    try:
        list = []
        data = session_pub.query(md_pub.TbWellprojStimeforcastBpnnModelinfo.Model_Name).filter().all()
        for i in range(len(data)):
            list.append(data[i][0])
        print('添加数据成功')
        return list
    except:
        print('添加数据失败')
        session_pub.rollback()
        return False


# 读取单元名称、完井日期 tb_wellproj_stimeforcast_bpnn
def stimforcast_UnitName_Date_Sample():
    try:
        DvpUnit_Name = session_pub.query(md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_Name).filter().all()
        date = session_pub.query(md_pub.TbWellprojStimeforcastBpnn.End_Date).filter().all()
        unitName = []
        Date = []
        for i in range(len(DvpUnit_Name)):
            unitName.append(DvpUnit_Name[i][0])
        for i in range(len(date)):
            Date.append(date[i][0])
        minDate = maxDate = Date[0]
        for i in range(len(Date)):
            if Date[i] > maxDate:
                maxDate = Date[i]
            if Date[i] < minDate:
                minDate = Date[i]
        id = 1
        finalData = []
        for i in range(len(unitName)):
            oildata = session_pub.query(md_pub.TbWellbasic.Well_Name).filter(
                md_pub.TbWellbasic.Belong_DvpUnit_Name == unitName[i]
            ).all()
            if len(oildata) != 0:
                Datalist = []
                for k in range(len(oildata)):
                    Datalist.append(
                        {
                            'title': session_pub.query(md_pub.TbWellprojStimeforcastBpnn.Well_Name).filter(
                                md_pub.TbWellprojStimeforcastBpnn.Well_Name == oildata[k][0]).one()[0],
                            'id': id + 1 + k
                        }
                    )
                Data = {
                    'title': unitName[i],
                    'id': id,
                    'chidren:': Datalist
                }
                id += len(Datalist) + 1
                finalData.append(Data)
        session_pub.close()
        return {'data': finalData, 'date': [minDate, maxDate]}
    except:
        return False


# 存库 BP神经网络预测数据表 tb_wellproj_stimeforcast_bpnn
def stimeforcast_store(save_BP_data_json):
    try:
        for i in range(len(save_BP_data_json.data)):
            # print(save_BP_data_json.data[i]["parse_Data"])
            # 处理上传的数据
            save_BP_data_json.data[i].update(
                save_BP_data_json.data[i]["parse_Data"]
            )
            del (save_BP_data_json.data[i]["parse_Data"])
            # print(save_BP_data_json.data[i])
            # 判断新增还是删除
            existed_data = session_pub.query(
                md_pub.TbWellprojStimeforcastBpnn
            ).filter(
                md_pub.TbWellprojStimeforcastBpnn.Well_Name == save_BP_data_json.data[i]['Well_Name'],
                md_pub.TbWellprojStimeforcastBpnn.Model_Name == save_BP_data_json.data[i]['Model_Name'],
                md_pub.TbWellprojStimeforcastBpnn.StimType_Name == save_BP_data_json.data[i]['StimType_Name']
            ).first()
            if existed_data:
                existed_data = existed_data.__dict__
                existed_data.update(save_BP_data_json.data[i])
                add_data = existed_data
                session_pub.query(
                    md_pub.TbWellprojStimeforcastBpnn
                ).filter(
                    md_pub.TbWellprojStimeforcastBpnn.Well_Name == save_BP_data_json.data[i]['Well_Name'],
                    md_pub.TbWellprojStimeforcastBpnn.Model_Name == save_BP_data_json.data[i]['Model_Name'],
                    md_pub.TbWellprojStimeforcastBpnn.StimType_Name == save_BP_data_json.data[i]['StimType_Name']
                ).delete()
            else:
                add_data = {
                    'Term_validity': 0,
                    'X5': 0.0,
                    'X13': 0.0,
                    'X19': 0.0,
                    'X27': 0.0,
                    'Total_IncreaseOil': 0,
                    'X6': 0.0,
                    'X14': 0.0,
                    'X20': 0.0,
                    'X28': 0.0,
                    'Sample_Sign': '',
                    'X7': 0.0,
                    'X15': 0.0,
                    'X21': 0.0,
                    'X29': 0.0,
                    'X1': 0.0,
                    'X8': 0.0,
                    'X22': 0.0,
                    'X30': 0.0,
                    'X2': 0.0,
                    'X9': 0.0,
                    'X16': 0.0,
                    'X23': 0.0,
                    'Well_Name': '',
                    'X3': 0.0,
                    'X10': 0.0,
                    'X17': 0.0,
                    'X24': 0.0,
                    'StimType_Name': '',
                    'X4': 0.0,
                    'X11': 0.0,
                    'X18': 0.0,
                    'X25': 0.0,
                    'End_Date': '',
                    'X12': 0.0,
                    'X26': 0.0
                }
                add_data.update(save_BP_data_json.data[i])

            # 新增数据
            data = md_pub.TbWellprojStimeforcastBpnn(
                Model_Name=add_data['Model_Name'],
                Well_Name=add_data['Well_Name'],
                StimType_Name=add_data['StimType_Name'],
                End_Date=add_data['End_Date'],
                Term_validity=add_data['Term_validity'],
                Total_IncreaseOil=add_data['Total_IncreaseOil'],
                Sample_Sign=add_data['Sample_Sign'],
                X1=add_data['X1'],
                X2=add_data['X2'],
                X3=add_data['X3'],
                X4=add_data['X4'],
                X5=add_data['X5'],
                X6=add_data['X6'],
                X7=add_data['X7'],
                X8=add_data['X8'],
                X9=add_data['X9'],
                X10=add_data['X10'],
                X11=add_data['X11'],
                X12=add_data['X12'],
                X13=add_data['X13'],
                X14=add_data['X14'],
                X15=add_data['X15'],
                X16=add_data['X16'],
                X17=add_data['X17'],
                X18=add_data['X18'],
                X19=add_data['X19'],
                X20=add_data['X20'],
                X21=add_data['X21'],
                X22=add_data['X22'],
                X23=add_data['X23'],
                X24=add_data['X24'],
                X25=add_data['X25'],
                X26=add_data['X26'],
                X27=add_data['X27'],
                X28=add_data['X28'],
                X29=add_data['X29'],
                X30=add_data['X30']
            )
            session_pub.add(data)
            session_pub.commit()
        print('添加数据成功')
        session_pub.close()
        return True
    except Exception as e:
        print(e)
        print('添加数据失败')
        return False


# 读库 BP神经网络预测数据表 tb_wellproj_stimeforcast_bpnn
def read_stimeforcast(Unit_Name, Model_Name, start_date, end_date):
    try:
        datalist = []
        well_name = session_pub.query(
            md_pub.TbWellbasic.Well_Name
        ).filter(
            md_pub.TbWellbasic.Belong_DvpUnit_Name.in_(
                Unit_Name)
        ).all()
        if len(well_name):
            well_name = [well[0] for well in well_name]
        else:
            return None
        for i in range(len(well_name)):
            a = session_pub.query(
                md_pub.TbWellprojStimeforcastBpnn
            ).filter(
                md_pub.TbWellprojStimeforcastBpnn.Well_Name == well_name[i],
                md_pub.TbWellprojStimeforcastBpnn.Model_Name == Model_Name,
                md_pub.TbWellprojStimeforcastBpnn.End_Date >= start_date,
                md_pub.TbWellprojStimeforcastBpnn.End_Date <= end_date,
            ).all()
            if len(a):
                datalist.extend(a)

        print('读取数据成功')
        print(datalist)
        session_pub.close()
        return datalist
    except Exception as e:
        print(e.__traceback__.tb_lineno)

        # return e
        return False


# 将数据删除 BP神经网络预测数据表 tb_wellproj_stimeforcast_bpnn
def stimeforcast_delete(well_name, end_data):
    try:
        session_pub.query(md_pub.TbWellprojStimeforcastBpnn).filter(
            md_pub.TbWellprojStimeforcastBpnn.Well_Name == well_name and
            md_pub.TbWellprojStimeforcastBpnn.End_Date == end_data).delete()
        session_pub.commit()
        print('删除数据成功')
        session_pub.close()
        return True
    except:
        print('删除数据失败')
        return False


# 新建模型 BP神经网络预测数据表 tb_wellproj_stimeforcast_bpnn_modelinfo
def stimeforcast_newModel(Model_Name, Creater_Name, StimType_Name, Remark):
    try:
        CreatTime = ModifyTime = str(datetime.datetime.now().year) + '-' + str(datetime.datetime.now().month) + '-' \
                                 + str(datetime.datetime.now().day)
        data = md_pub.TbWellprojStimeforcastBpnnModelinfo(
            Model_Name=Model_Name,
            StimType_Name=StimType_Name,
            Creater=Creater_Name,
            Creat_Time=CreatTime,
            Modify_Time=ModifyTime,
            Train_Time='',
            Remark=Remark
        )
        extra_data = md_pub.TbWellprojStimeforcastBpnnPara(
            Model_Name=Model_Name,
            DvpUnit_ID="",
            DvpUnit_Name="",
            Stimulate_Date="",
            Sample_Num=0,
            Train_Num=0,
            Test_Num=0,
            Input_Para="",
            Output_Para="",
            HideLayer_CoreNum=0,
            HideLayer_Active="",
            OutputLayer_Active="",
            Learn_Rate=0,
            Target_error=0,
            MaxEpoch=0,
            Model_Paras="",
            Compared_Result="",
            Real_Epoch=0,
            Epoch_Error=0,
            Epoch_Duration=0,
            Absolute_ErrorValidity=0,
            Relative_Error_Validity=0,
            Cum_OilIncreAbsoluteError=0,
            Cum_OilIncreRelativeError=0,
        )
        session_pub.add(data)
        session_pub.add(extra_data)
        session_pub.commit()
        session_pub.close()
        print('新建模型成功')
        return True
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)
        session_pub.rollback()
        print('新建模型失败')
        return False


# 读取模型信息 BP神经网络预测数据表 tb_wellproj_stimeforcast_bpnn
def stimeforcast_readModelinfo(Model_Name):
    try:
        data = session_pub.query(
            md_pub.TbWellprojStimeforcastBpnnModelinfo
        ).filter(
            md_pub.TbWellprojStimeforcastBpnnModelinfo.Model_Name == Model_Name
        ).first()
        data = data.__dict__
        extra_data = session_pub.query(
            md_pub.TbWellprojStimeforcastBpnnPara.Compared_Result,
            md_pub.TbWellprojStimeforcastBpnnPara.Train_Num,
            md_pub.TbWellprojStimeforcastBpnnPara.Absolute_ErrorValidity,
            md_pub.TbWellprojStimeforcastBpnnPara.Relative_Error_Validity,
            md_pub.TbWellprojStimeforcastBpnnPara.Cum_OilIncreRelativeError,
            md_pub.TbWellprojStimeforcastBpnnPara.Cum_OilIncreAbsoluteError,
        ).filter(
            md_pub.TbWellprojStimeforcastBpnnPara.Model_Name == Model_Name
        ).first()
        # print(extra_data.keys())
        extra_data = dict(zip(extra_data.keys(), extra_data))
        # print(extra_data)
        data.update(extra_data)
        print('读取数据成功')
        session_pub.close()
        return data
    except Exception as e:
        print(e)
        return False


# BP神经网络训练 BP神经网络预测数据表 tb_wellproj_stimeforcast_bpnn_modelinfo
def stimeforcast_training(train_Data_BP, Model_Name, params):
    try:
        data = session_pub.query(
            md_pub.TbWellprojStimeforcastBpnn.X1,
            md_pub.TbWellprojStimeforcastBpnn.X2,
            md_pub.TbWellprojStimeforcastBpnn.X3,
            md_pub.TbWellprojStimeforcastBpnn.X4,
            md_pub.TbWellprojStimeforcastBpnn.X5,
            md_pub.TbWellprojStimeforcastBpnn.X6,
            md_pub.TbWellprojStimeforcastBpnn.X7,
            md_pub.TbWellprojStimeforcastBpnn.X8,
            md_pub.TbWellprojStimeforcastBpnn.X9,
            md_pub.TbWellprojStimeforcastBpnn.X10,
            md_pub.TbWellprojStimeforcastBpnn.X11,
            md_pub.TbWellprojStimeforcastBpnn.X12,
            md_pub.TbWellprojStimeforcastBpnn.X13,
            md_pub.TbWellprojStimeforcastBpnn.X14,
            md_pub.TbWellprojStimeforcastBpnn.X15,
            md_pub.TbWellprojStimeforcastBpnn.X16,
            md_pub.TbWellprojStimeforcastBpnn.X17,
            md_pub.TbWellprojStimeforcastBpnn.X18,
            md_pub.TbWellprojStimeforcastBpnn.X19,
            md_pub.TbWellprojStimeforcastBpnn.X20,
            md_pub.TbWellprojStimeforcastBpnn.X21,
            md_pub.TbWellprojStimeforcastBpnn.X22,
            md_pub.TbWellprojStimeforcastBpnn.X23,
            md_pub.TbWellprojStimeforcastBpnn.X24,
            md_pub.TbWellprojStimeforcastBpnn.X25,
            md_pub.TbWellprojStimeforcastBpnn.X26,
            md_pub.TbWellprojStimeforcastBpnn.X27,
            md_pub.TbWellprojStimeforcastBpnn.X28,
            md_pub.TbWellprojStimeforcastBpnn.X29,
            md_pub.TbWellprojStimeforcastBpnn.X30,
            md_pub.TbWellprojStimeforcastBpnn.Term_validity,
            md_pub.TbWellprojStimeforcastBpnn.Total_IncreaseOil,
            md_pub.TbWellprojStimeforcastBpnn.Well_Name,
            md_pub.TbWellprojStimeforcastBpnn.End_Date
        ).filter(
            md_pub.TbWellprojStimeforcastBpnn.Sample_Sign == '1-样' or
            md_pub.TbWellprojStimeforcastBpnn.Sample_Sign == '2-样测',
            md_pub.TbWellprojStimeforcastBpnn.Model_Name == Model_Name
        ).all()
        if not data:
            return False
        # 将结果转化为dict类型
        data = [dict(zip(d.keys(), d)) for d in data]
        DataX = []
        DataY = []

        for d in data:
            temp_l1 = []
            temp_l2 = []
            for key in params:
                temp_l1.append(d[key])
            temp_l2.extend([d['Term_validity'], d['Total_IncreaseOil']])
            DataX.append(temp_l1)
            DataY.append(temp_l2)

        # print(DataX)
        # print(DataY)
        # for i in range(len(a)):
        #     list1 = []
        #     list2 = []
        #     for j in range(12):
        #         list1.append(a[i][j])
        #     list2.append(a[i][12])
        #     list2.append(a[i][13])
        #     DataX.append(list1)
        #     DataY.append(list2)

        mm = MinMaxScaler()

        Term_validity = []
        Total_IncreaseOil = []
        for i in range(len(DataY)):
            Term_validity.append(DataY[i][0])
            Total_IncreaseOil.append(DataY[i][1])
        Term_validity_Max = max(Term_validity)
        Term_validity_Min = min(Term_validity)
        Total_IncreaseOil_Max = max(Total_IncreaseOil)
        Total_IncreaseOil_Min = min(Total_IncreaseOil)

        modelname = train_Data_BP.Model_Name  # 模型名称
        trainDataX = mm.fit_transform(DataX)  # 训练数据集X
        trainDatay = mm.fit_transform(DataY)  # 训练数据集y
        BPparalist = train_Data_BP.BPparalist

        inputNeuronsNum = len(params)  # 输入层神经元数量
        hidenNeuronsNum = int(BPparalist[0])  # 隐藏层神经元数量
        outputNeuronsNum = 2  # 输出层神经元数量
        hidenFunction = BPparalist[1]  # 隐藏层激励函数
        outputFunction = BPparalist[2]  # 输出层激励函数
        learningRate = float(BPparalist[3])  # 学习率
        targetError = float(BPparalist[4])  # 目标误差
        maxIterationsNums = int(BPparalist[5])  # 最大迭代次数

        starttime = time.time()
        nn = BPNeuralNetwork()
        nn.setup(inputNeuronsNum, hidenNeuronsNum, outputNeuronsNum)
        trainsData = nn.train(trainDataX, trainDatay, maxIterationsNums, learningRate, targetError)
        predicData = []
        for k in trainDataX:
            predicData.append(nn.predict(k, 'sigmoid', 'sigmoid'))

        for i in range(len(predicData)):
            list = []
            list.append(math.ceil(predicData[i][0] * (Term_validity_Max - Term_validity_Min) + Term_validity_Min))
            list.append(float(
                format(predicData[i][1] * (Total_IncreaseOil_Max - Total_IncreaseOil_Min) + Total_IncreaseOil_Min,
                       '.4f')))
            predicData[i] = list

        endtime = time.time()
        dtime = endtime - starttime
        dtime = ('%.5f' % dtime)
        trainingTime = str(datetime.datetime.now().year) + '-' + str(datetime.datetime.now().month) + '-' \
                       + str(datetime.datetime.now().day)
        session_pub.query(md_pub.TbWellprojStimeforcastBpnnModelinfo). \
            filter(md_pub.TbWellprojStimeforcastBpnnModelinfo.Model_Name == modelname). \
            update({md_pub.TbWellprojStimeforcastBpnnModelinfo.Train_Time: trainingTime})
        session_pub.commit()
        session_pub.close()
        print('添加数据成功')
        sendData = {'trainsData': trainsData, 'predicData': predicData, 'runningTime': dtime, 'trainingTime': trainingTime, 'oriData': data}
        return sendData

    except Exception as e:
        print(e, e.__traceback__.tb_lineno)
        return False


# BP神经网络训练 BP神经网络参数储存表 tb_wellproj_stimeforcast_bpnn_paras
def stimeforcast_saveparas(save_data):
    try:
        session_pub.query(
            md_pub.TbWellprojStimeforcastBpnnPara
        ).filter(
            md_pub.TbWellprojStimeforcastBpnnPara.Model_Name == save_data.Model_Name
        ).delete()
        data = md_pub.TbWellprojStimeforcastBpnnPara(
            Model_Name=save_data.Model_Name,
            DvpUnit_ID=save_data.DvpUnit_ID,
            DvpUnit_Name=save_data.DvpUnit_Name,
            Stimulate_Date=save_data.Stimulate_Date,
            Sample_Num=save_data.Sample_Num,
            Train_Num=save_data.Train_Num,
            Test_Num=save_data.Test_Num,
            Input_Para=save_data.Input_Para,
            Output_Para='',
            HideLayer_CoreNum=save_data.HideLayer_CoreNum,
            HideLayer_Active=save_data.HideLayer_Active,
            OutputLayer_Active=save_data.OutputLayer_Active,
            Learn_Rate=save_data.Learn_Rate,
            Target_error=save_data.Target_error,
            MaxEpoch=save_data.MaxEpoch,
            Model_Paras=save_data.Model_Paras,
            Compared_Result=save_data.Compared_Result,
            Real_Epoch=save_data.Real_Epoch,
            Epoch_Error=save_data.Epoch_Error,
            Epoch_Duration=save_data.Epoch_Duration,
            Absolute_ErrorValidity=save_data.Absolute_ErrorValidity,
            Relative_Error_Validity=save_data.Relative_Error_Validity,
            Cum_OilIncreAbsoluteError=save_data.Cum_OilIncreAbsoluteError,
            Cum_OilIncreRelativeError=save_data.Cum_OilIncreRelativeError,
        )
        session_pub.add(data)
        session_pub.commit()
        return True
    except Exception as e:
        print(e, e.__traceback__.tb_lineno)
        return False

# 保存BP神经网络预测模型信息表
def save_model_info_data(model_info):
    try:
        session_pub.query(
            md_pub.TbWellprojStimeforcastBpnnModelinfo
        ).filter(
            md_pub.TbWellprojStimeforcastBpnnModelinfo.Model_Name == model_info.Model_Name
        ).update({
            md_pub.TbWellprojStimeforcastBpnnModelinfo.StimType_Name: model_info.StimType_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.Effect_Paramters_Number: model_info.Effect_Paramters_Number,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.Modify_Time: datetime.datetime.now().strftime(
                "%Y-%m-%d %H:%M:%S"),
            md_pub.TbWellprojStimeforcastBpnnModelinfo.Remark: model_info.Remark,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X1_Paramters_Name: model_info.X1_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X2_Paramters_Name: model_info.X2_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X3_Paramters_Name: model_info.X3_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X4_Paramters_Name: model_info.X4_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X5_Paramters_Name: model_info.X5_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X6_Paramters_Name: model_info.X6_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X7_Paramters_Name: model_info.X7_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X8_Paramters_Name: model_info.X8_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X9_Paramters_Name: model_info.X9_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X10_Paramters_Name: model_info.X10_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X11_Paramters_Name: model_info.X11_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X12_Paramters_Name: model_info.X12_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X13_Paramters_Name: model_info.X13_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X14_Paramters_Name: model_info.X14_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X15_Paramters_Name: model_info.X15_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X16_Paramters_Name: model_info.X16_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X17_Paramters_Name: model_info.X17_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X18_Paramters_Name: model_info.X18_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X19_Paramters_Name: model_info.X19_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X20_Paramters_Name: model_info.X20_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X21_Paramters_Name: model_info.X21_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X22_Paramters_Name: model_info.X22_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X23_Paramters_Name: model_info.X23_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X24_Paramters_Name: model_info.X24_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X25_Paramters_Name: model_info.X25_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X26_Paramters_Name: model_info.X26_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X27_Paramters_Name: model_info.X27_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X28_Paramters_Name: model_info.X28_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X29_Paramters_Name: model_info.X29_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X30_Paramters_Name: model_info.X30_Paramters_Name,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X1_Paramters_Unit: model_info.X1_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X2_Paramters_Unit: model_info.X2_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X3_Paramters_Unit: model_info.X3_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X4_Paramters_Unit: model_info.X4_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X5_Paramters_Unit: model_info.X5_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X6_Paramters_Unit: model_info.X6_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X7_Paramters_Unit: model_info.X7_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X8_Paramters_Unit: model_info.X8_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X9_Paramters_Unit: model_info.X9_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X10_Paramters_Unit: model_info.X10_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X11_Paramters_Unit: model_info.X11_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X12_Paramters_Unit: model_info.X12_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X13_Paramters_Unit: model_info.X13_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X14_Paramters_Unit: model_info.X14_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X15_Paramters_Unit: model_info.X15_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X16_Paramters_Unit: model_info.X16_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X17_Paramters_Unit: model_info.X17_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X18_Paramters_Unit: model_info.X18_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X19_Paramters_Unit: model_info.X19_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X20_Paramters_Unit: model_info.X20_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X21_Paramters_Unit: model_info.X21_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X22_Paramters_Unit: model_info.X22_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X23_Paramters_Unit: model_info.X23_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X24_Paramters_Unit: model_info.X24_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X25_Paramters_Unit: model_info.X25_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X26_Paramters_Unit: model_info.X26_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X27_Paramters_Unit: model_info.X27_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X28_Paramters_Unit: model_info.X28_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X29_Paramters_Unit: model_info.X29_Paramters_Unit,
            md_pub.TbWellprojStimeforcastBpnnModelinfo.X30_Paramters_Unit: model_info.X30_Paramters_Unit
        })
        session_pub.commit()
        return True
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)
        session_pub.rollback()
        return False


def fun(datas, name):
    try:
        list = []
        for data in datas:
            if data['Belong_DvpUnit_Name'] == name:
                list.append({
                    "id": data['DvpUnit_ID'],
                    "title": data['DvpUnit_Name'],
                    "rank": data['DvpUnit_Rank'],
                    "children": fun(datas, data['DvpUnit_Name'])
                })
        for item in list:
            if len(item['children']) == 0:
                del item['children']
        return list
    except Exception as e:
        print(e)


# BP神经网络预测 读取规划方案、单元名称、起止年度
def stimeforcast_read_Plan_Unit_Year():
    try:
        DvpUnit_OilStim_PlanProj_Name = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojMessage.DvpUnit_OilStim_PlanProj_Name).all()

        units = session_pub.query(
            md_pub.TbDvpunitbaisc.DvpUnit_Name,
            md_pub.TbDvpunitbaisc.Belong_DvpUnit_Name,
            md_pub.TbDvpunitbaisc.DvpUnit_Rank,
            md_pub.TbDvpunitbaisc.DvpUnit_ID
        ).all()
        all_dvp = [dict(zip(unit.keys(), unit)) for unit in units]

        StimPlanProj_StartYear = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_StartYear).filter().all()
        StimPlanProj_EndYear = session_pub.query(
            md_pub.TbYearDvpunitOilstimPlanprojMessage.StimPlanProj_EndYear).filter().all()
        planName = []
        dvp_tree = list()
        dvp_list = sorted(all_dvp, key=lambda x: x["DvpUnit_Rank"])
        while len(dvp_list):
            item = dvp_list[0]
            temp_dict = dict()
            temp_dict.update({
                'id': item['DvpUnit_ID'],
                'title': item['DvpUnit_Name'],
            })
            child = dvpunit_name_tree(dvp_list, item['DvpUnit_Name'])
            if child:
                temp_dict.update({
                    'children': child
                })
            dvp_tree.append(temp_dict)
            del_name = set()
            new_list = list()
            del_name.add(item['DvpUnit_Name'])
            for x in dvp_list:
                if x['Belong_DvpUnit_Name'] in del_name or x['DvpUnit_Name'] in del_name:
                    del_name.add(x['DvpUnit_Name'])
                else:
                    new_list.append(x)
            dvp_list = new_list

        Year = []
        for i in range(len(DvpUnit_OilStim_PlanProj_Name)):
            planName.append(DvpUnit_OilStim_PlanProj_Name[i][0])

        for i in range(len(StimPlanProj_StartYear)):
            Year.append(int(StimPlanProj_StartYear[i][0]))
        for i in range(len(StimPlanProj_EndYear)):
            Year.append(int(StimPlanProj_EndYear[i][0]))
        minYear = Year[0]
        maxYear = Year[0]
        for i in range(len(Year)):
            if Year[i] < minYear:
                minYear = Year[i]
            if Year[i] > maxYear:
                maxYear = Year[i]
        return {
            "planName": planName if planName else None,
            "unitName": dvp_tree if dvp_tree else None,
            "yearRange": [minYear, maxYear]
        }
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)
        return False


# BP神经网络预测 读取预测参数
def stimeforcast_loadPredictingData(read_data):
    try:
        planName = read_data.planName
        unitName = read_data.unitName
        startYear = read_data.startYear
        EndYear = read_data.EndYear
        oilName = []
        for i in range(len(unitName)):
            oilName.append(session_pub.query(md_pub.TbWellbasic.Well_Name).filter(
                md_pub.TbWellbasic.Belong_DvpUnit_Name == unitName[i]).all())
        datalist = []
        for i in range(len(oilName)):
            if len(oilName[i]) == 0:
                continue
            for k in range(len(oilName[i])):
                for j in range(len(planName)):
                    data = session_pub.query(
                        md_pub.TbWellprojStimeforcastBpnnPred
                    ).filter(
                        md_pub.TbWellprojStimeforcastBpnnPred.DvpUnit_OilStim_PlanProj_Name == planName[j],
                        md_pub.TbWellprojStimeforcastBpnnPred.Well_Name == oilName[i][k][0],
                        md_pub.TbWellprojStimeforcastBpnnPred.Year <= EndYear,
                        md_pub.TbWellprojStimeforcastBpnnPred.Year >= startYear
                    ).all()
                    if len(data) != 0:
                        datalist.extend(data)
        session_pub.close()
        return datalist
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)
        return False


# BP神经网络预测 读取模型名称
def stimeforcast_loadModels():
    try:
        data = session_pub.query(md_pub.TbWellprojStimeforcastBpnnPara.Model_Name).filter().all()
        session_pub.close()
        return data
    except:
        return False


# BP神经网络预测 预测
def stimeforcast_predict(predData):
    try:
        model = session_pub.query(
            md_pub.TbWellprojStimeforcastBpnnPara.HideLayer_CoreNum,
            md_pub.TbWellprojStimeforcastBpnnPara.Model_Paras,
            md_pub.TbWellprojStimeforcastBpnnPara.Input_Para
        ).filter(
            md_pub.TbWellprojStimeforcastBpnnPara.Model_Name == predData.modelname
        ).all()
        session_pub.close()
        data = predData.data

        hidenNeuronsNum = model[0][0]
        Model_Paras = eval(model[0][1])
        Input_Paras = eval(model[0][2])
        datemin = Input_Paras[0]['min']
        datemax = Input_Paras[0]['max']
        oilmin = Input_Paras[1]['min']
        oilmax = Input_Paras[1]['max']
        input_weight = Model_Paras["input"]
        output_weight = Model_Paras["ouput"]

        # 归一化
        datalist = []
        for sub_data in data:
            temp_list = []
            for key in sub_data['parse_Data'].keys():
                temp_list.append(sub_data['parse_Data'][key])
            datalist.append(temp_list)

        mm = MinMaxScaler()
        normalizedData = mm.fit_transform(datalist)

        nn = BPNeuralNetwork()
        nn.input_weights = input_weight,
        nn.output_weights = output_weight
        nn.setup(len(data[0]['parse_Data']), hidenNeuronsNum, 2)
        predicDataNor = []
        predicData = []
        for k in normalizedData:
            nn.predict(k, 'sigmoid', 'sigmoid')
            predicDataNor.append(nn.predict(k, 'sigmoid', 'sigmoid'))
        for i in range(len(predicDataNor)):
            print(predicDataNor[i][0], type(datemax), type(datemin))
            list = [math.ceil(predicDataNor[i][0] * (float(datemax) - float(datemin)) + float(datemin)),
                    round(predicDataNor[i][1] * (float(oilmax) - float(oilmin)) + float(oilmin), 2)]
            predicData.append(list)
        print(predicDataNor)
        finaldata = []
        for i in range(len(predicData)):
            obj = {
                "ValidPeriod_Forecast": predicData[i][0],
                "IncreOil_Forecast": predicData[i][1],
                "BP_Model_Name": predData.modelname,
                "Stim_Frac_Forecast_Date": str(datetime.datetime.now().year) + '-' + str(datetime.datetime.now().month) + '-' + str(datetime.datetime.now().day)
            }
            obj.update(data[i])
            finaldata.append(obj)
        return finaldata
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)
        return False

# BP神经网络预测 存库
def stimeforcast_predictsave(save_BP_data_json):
    try:
        for i in range(len(save_BP_data_json.data)):
            # print(save_BP_data_json.data[i]["parse_Data"])
            # 处理上传的数据
            save_BP_data_json.data[i].update(
                save_BP_data_json.data[i]["parse_Data"]
            )
            del (save_BP_data_json.data[i]["parse_Data"])
            # print(save_BP_data_json.data[i])
            # 判断新增还是删除
            existed_data = session_pub.query(
                md_pub.TbOilwellFracResultForcastBpnnPlanprojDatum
            ).filter(
                md_pub.TbOilwellFracResultForcastBpnnPlanprojDatum.Well_Name == save_BP_data_json.data[i]['Well_Name'],
                md_pub.TbOilwellFracResultForcastBpnnPlanprojDatum.DvpUnit_OilStim_PlanProj_Name == save_BP_data_json.data[i]['DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbOilwellFracResultForcastBpnnPlanprojDatum.Year == save_BP_data_json.data[i]['Year']
            ).first()
            if existed_data:
                existed_data = existed_data.__dict__
                existed_data.update(save_BP_data_json.data[i])
                add_data = existed_data
                session_pub.query(
                    md_pub.TbOilwellFracResultForcastBpnnPlanprojDatum
                ).filter(
                    md_pub.TbOilwellFracResultForcastBpnnPlanprojDatum.Well_Name == save_BP_data_json.data[i][
                        'Well_Name'],
                    md_pub.TbOilwellFracResultForcastBpnnPlanprojDatum.DvpUnit_OilStim_PlanProj_Name ==
                    save_BP_data_json.data[i]['DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbOilwellFracResultForcastBpnnPlanprojDatum.Year == save_BP_data_json.data[i]['Year']
                ).delete()
            else:
                add_data = {
                    'DvpUnit_OilStim_PlanProj_Name': "",
                    'X5': 0.0,
                    'X13': 0.0,
                    'X19': 0.0,
                    'X27': 0.0,
                    'Well_Name': "",
                    'X6': 0.0,
                    'X14': 0.0,
                    'X20': 0.0,
                    'X28': 0.0,
                    'Year': '',
                    'X7': 0.0,
                    'X15': 0.0,
                    'X21': 0.0,
                    'X29': 0.0,
                    'X1': 0.0,
                    'X8': 0.0,
                    'X22': 0.0,
                    'X30': 0.0,
                    'X2': 0.0,
                    'X9': 0.0,
                    'X16': 0.0,
                    'X23': 0.0,
                    'ValidPeriod_Forecast': '',
                    'X3': 0.0,
                    'X10': 0.0,
                    'X17': 0.0,
                    'X24': 0.0,
                    'IncreOil_Forecast': '',
                    'X4': 0.0,
                    'X11': 0.0,
                    'X18': 0.0,
                    'X25': 0.0,
                    'BP_Model_Name': '',
                    'Stim_Frac_Forecast_Date': '',
                    'X12': 0.0,
                    'X26': 0.0
                }
                add_data.update(save_BP_data_json.data[i])

            # 新增数据
            data = md_pub.TbOilwellFracResultForcastBpnnPlanprojDatum(
                Well_Name=add_data['Well_Name'],
                DvpUnit_OilStim_PlanProj_Name=add_data['DvpUnit_OilStim_PlanProj_Name'],
                Year=add_data['Year'],
                ValidPeriod_Forecast=add_data['ValidPeriod_Forecast'],
                IncreOil_Forecast=add_data['IncreOil_Forecast'],
                BP_Model_Name=add_data['BP_Model_Name'],
                Stim_Frac_Forecast_Date=add_data['Stim_Frac_Forecast_Date'],
                X1=add_data['X1'],
                X2=add_data['X2'],
                X3=add_data['X3'],
                X4=add_data['X4'],
                X5=add_data['X5'],
                X6=add_data['X6'],
                X7=add_data['X7'],
                X8=add_data['X8'],
                X9=add_data['X9'],
                X10=add_data['X10'],
                X11=add_data['X11'],
                X12=add_data['X12'],
                X13=add_data['X13'],
                X14=add_data['X14'],
                X15=add_data['X15'],
                X16=add_data['X16'],
                X17=add_data['X17'],
                X18=add_data['X18'],
                X19=add_data['X19'],
                X20=add_data['X20'],
                X21=add_data['X21'],
                X22=add_data['X22'],
                X23=add_data['X23'],
                X24=add_data['X24'],
                X25=add_data['X25'],
                X26=add_data['X26'],
                X27=add_data['X27'],
                X28=add_data['X28'],
                X29=add_data['X29'],
                X30=add_data['X30']
            )
            session_pub.add(data)
            session_pub.commit()
        print('添加数据成功')
        session_pub.close()
        return True
    except Exception as e:
        print(e)
        print('添加数据失败')
        return False



# BP神经网络预测 存油井措施方案效果预测表
def stimeforcast_predictsavetopredtabel(senddata):
    try:
        for i in range(len(senddata.data)):
            session_pub.query(md_pub.TbOilwellstimPlanprojForecast).filter(
                md_pub.TbOilwellstimPlanprojForecast.Well_Name == senddata.data[i]["Well_Name"]).delete()
            StimType_Name = session_pub.query(
                md_pub.TbWellprojStimeforcastBpnnModelinfo.StimType_Name
            ).filter(
                md_pub.TbWellprojStimeforcastBpnnModelinfo.Model_Name == senddata.data[i]['Model_Name']
            ).first()
            if not StimType_Name:
                return False
            adata = md_pub.TbOilwellstimPlanprojForecast(
                DvpUnit_OilStim_PlanProj_Name=senddata.data[i]["DvpUnit_OilStim_PlanProj_Name"],
                Well_Name=senddata.data[i]["Well_Name"],
                Year=senddata.data[i]["Year"],
                StimType_Name=StimType_Name[0],
                Forecast_Method='BP神经网络',
                Stim_ValidPeriod=senddata.data[i]["Stim_ValidPeriod"],
                Stim_IncreOil=senddata.data[i]["Stim_IncreOil"],
                Update_Date=str(datetime.datetime.now().year) + '-' + str(datetime.datetime.now().month) + '-' +
                            str(datetime.datetime.now().day),
            )
            session_pub.add(adata)
            session_pub.commit()
        session_pub.close()
        return True
    except Exception as e:
        print(e)
        print(e.__traceback__.tb_lineno)
        return False


def bp_predict_save_params(add, delete):
    result = {
        "add_success": [],
        "update_success": [],
        "delete_success": [],
    }
    try:
        for i in range(len(add)):
            # print(save_BP_data_json.data[i]["parse_Data"])
            # 处理上传的数据
            add[i].update(
                add[i]["parse_Data"]
            )
            del (add[i]["parse_Data"])
            # print(save_BP_data_json.data[i])
            # 判断新增还是删除
            existed_data = session_pub.query(
                md_pub.TbWellprojStimeforcastBpnnPred
            ).filter(
                md_pub.TbWellprojStimeforcastBpnnPred.DvpUnit_OilStim_PlanProj_Name == add[i]['DvpUnit_OilStim_PlanProj_Name'],
                md_pub.TbWellprojStimeforcastBpnnPred.Well_Name == add[i]['Well_Name'],
                md_pub.TbWellprojStimeforcastBpnnPred.Year == add[i]['Year']
            ).first()
            if existed_data:
                # 若已存在，则用提交的需要修改的参数把已有的对应的参数覆盖
                existed_data = existed_data.__dict__
                existed_data.update(add[i])
                add_data = existed_data
                session_pub.query(
                    md_pub.TbWellprojStimeforcastBpnnPred
                ).filter(
                    md_pub.TbWellprojStimeforcastBpnnPred.DvpUnit_OilStim_PlanProj_Name == add[i]['DvpUnit_OilStim_PlanProj_Name'],
                    md_pub.TbWellprojStimeforcastBpnnPred.Well_Name == add[i]['Well_Name'],
                    md_pub.TbWellprojStimeforcastBpnnPred.Year == add[i]['Year']
                ).delete()
            else:
                add_data = {
                    'DvpUnit_OilStim_PlanProj_Name': "",
                    'X5': 0.0,
                    'X13': 0.0,
                    'X19': 0.0,
                    'X27': 0.0,
                    'Year': "",
                    'X6': 0.0,
                    'X14': 0.0,
                    'X20': 0.0,
                    'X28': 0.0,
                    'X7': 0.0,
                    'X15': 0.0,
                    'X21': 0.0,
                    'X29': 0.0,
                    'X1': 0.0,
                    'X8': 0.0,
                    'X22': 0.0,
                    'X30': 0.0,
                    'X2': 0.0,
                    'X9': 0.0,
                    'X16': 0.0,
                    'X23': 0.0,
                    'Well_Name': '',
                    'X3': 0.0,
                    'X10': 0.0,
                    'X17': 0.0,
                    'X24': 0.0,
                    'X4': 0.0,
                    'X11': 0.0,
                    'X18': 0.0,
                    'X25': 0.0,
                    'X12': 0.0,
                    'X26': 0.0
                }
                add_data.update(add[i])

            # 新增数据
            data = md_pub.TbWellprojStimeforcastBpnnPred(
                Well_Name=add_data['Well_Name'],
                DvpUnit_OilStim_PlanProj_Name=add_data['DvpUnit_OilStim_PlanProj_Name'],
                Year=add_data['Year'],
                X1=add_data['X1'],
                X2=add_data['X2'],
                X3=add_data['X3'],
                X4=add_data['X4'],
                X5=add_data['X5'],
                X6=add_data['X6'],
                X7=add_data['X7'],
                X8=add_data['X8'],
                X9=add_data['X9'],
                X10=add_data['X10'],
                X11=add_data['X11'],
                X12=add_data['X12'],
                X13=add_data['X13'],
                X14=add_data['X14'],
                X15=add_data['X15'],
                X16=add_data['X16'],
                X17=add_data['X17'],
                X18=add_data['X18'],
                X19=add_data['X19'],
                X20=add_data['X20'],
                X21=add_data['X21'],
                X22=add_data['X22'],
                X23=add_data['X23'],
                X24=add_data['X24'],
                X25=add_data['X25'],
                X26=add_data['X26'],
                X27=add_data['X27'],
                X28=add_data['X28'],
                X29=add_data['X29'],
                X30=add_data['X30']
            )
            session_pub.add(data)
            session_pub.commit()
            if existed_data:
                result["update_success"].append(add_data['Well_Name'])
            else:
                result["add_success"].append(add_data['Well_Name'])
        # if len(add):
        #     for datas in add:
        #         is_exist_result = session_pub.query(
        #             md_pub.TbWellprojStimeforcastBpnnPred.DvpUnit_OilStim_PlanProj_Name
        #             ).filter(
        #             md_pub.TbWellprojStimeforcastBpnnPred.Well_Name == datas['Well_Name']
        #         ).first()
        #         if not is_exist_result:
        #             session_pub.add(
        #                 md_pub.TbWellprojStimeforcastBpnnPred(
        #                     DvpUnit_OilStim_PlanProj_Name=datas['DvpUnit_OilStim_PlanProj_Name'],
        #                     Well_Name=datas['Well_Name'],
        #                     Year=datas['Year'],
        #                     X1=datas.get('X1', 0),
        #                     X2=datas.get('X2', 0),
        #                     X3=datas.get('X3', 0),
        #                     X4=datas.get('X4', 0),
        #                     X5=datas.get('X5', 0),
        #                     X6=datas.get('X6', 0),
        #                     X7=datas.get('X7', 0),
        #                     X8=datas.get('X8', 0),
        #                     X9=datas.get('X9', 0),
        #                     X10=datas.get('X10', 0),
        #                     X11=datas.get('X11', 0),
        #                     X12=datas.get('X12', 0),
        #                     X13=datas.get('X13', 0),
        #                     X14=datas.get('X14', 0),
        #                     X15=datas.get('X15', 0),
        #                     X16=datas.get('X16', 0),
        #                     X17=datas.get('X17', 0),
        #                     X18=datas.get('X18', 0),
        #                     X19=datas.get('X19', 0),
        #                     X20=datas.get('X20', 0),
        #                     X21=datas.get('X21', 0),
        #                     X22=datas.get('X22', 0),
        #                     X23=datas.get('X23', 0),
        #                     X24=datas.get('X24', 0),
        #                     X25=datas.get('X25', 0),
        #                     X26=datas.get('X26', 0),
        #                     X27=datas.get('X27', 0),
        #                     X28=datas.get('X28', 0),
        #                     X29=datas.get('X29', 0),
        #                     X30=datas.get('X30', 0)
        #                 )
        #             )
        #             session_pub.commit()
        #             result['add_success'].append(datas['Well_Name'])
        #         else:
        #             db_data = session_pub.query(
        #                 md_pub.TbWellprojStimeforcastBpnnPred
        #             ).filter(
        #                 md_pub.TbWellprojStimeforcastBpnnPred.Well_Name == datas['Well_Name']
        #             ).update({
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X1: datas.get('X1', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X2: datas.get('X2', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X3: datas.get('X3', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X4: datas.get('X4', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X5: datas.get('X5', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X6: datas.get('X6', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X7: datas.get('X7', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X8: datas.get('X8', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X9: datas.get('X9', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X10: datas.get('X10', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X11: datas.get('X11', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X12: datas.get('X12', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X13: datas.get('X13', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X14: datas.get('X14', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X15: datas.get('X15', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X16: datas.get('X16', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X17: datas.get('X17', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X18: datas.get('X18', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X19: datas.get('X19', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X20: datas.get('X20', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X21: datas.get('X21', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X22: datas.get('X22', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X23: datas.get('X23', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X24: datas.get('X24', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X25: datas.get('X25', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X26: datas.get('X26', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X27: datas.get('X27', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X28: datas.get('X28', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X29: datas.get('X29', 0),
        #                 md_pub.TbWellprojStimeforcastBpnnPred.X30: datas.get('X30', 0),
        #             })
        #
        #             session_pub.commit()
        #             result['update_success'].append(datas['Well_Name'])
        if delete and len(delete):
            for i in range(len(delete)):
                session_pub.query(
                    md_pub.TbWellprojStimeforcastBpnnPred
                ).filter(
                    md_pub.TbWellprojStimeforcastBpnnPred.Well_Name == delete[i]
                ).delete()
                session_pub.commit()
                result['delete_success'].append(delete[i])
        return result
    except Exception as e:
        session_pub.rollback()
        print(e)
        print(e.__traceback__.tb_lineno)
        return result


if __name__ == '__main__':
    # import time
    #
    # date = time.strftime("%Y-%m-%d", time.localtime())

    # stimeforcast_delete(well_name=123, end_data=2021-10-25)
    # read_stimeforcast(model_name='test', well_name=["dqw1-1"],start_date='2019-01-01', end_date='2020-10-23')
    # stimforcast_readModelName()
    # stimforcast_UnitName_Date_Sample('test')
    # stimeforcast_readModelinfo('test')
    # stimeforcast_training(Train_Data)
    # stimeforcast_loadPredictingData()
    # stimeforcast_read_Plan_Unit_Year()

    data = {
        "unitName": ['单元10', '单元1', '单元10086', '十四五规划']
    }
